package lrscp.lib.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import lrscp.lib.Log;

public class DbUtils {

    public static Connection sqliteGetConnection(String dbFileName) {
        Connection connection = null;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbFileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void createMapTable(Statement sql, String table) throws SQLException {
        sql.executeUpdate("create table if not exists " + table + " (key, value)");
    }

    public static int getInt(Statement sql, String table, String key, int defaultValue) {
        String s = String.format("select * from %1s where key='%2s'", table, key);
        ResultSet rs;
        try {
            rs = sql.executeQuery(s);
            if (rs.next()) return rs.getInt("value");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return defaultValue;
    }

    public static void putInt(Statement sql, String table, String key) {
        String s = String.format("delete from %1s where key='%2s';", table, key);
        try {
            sql.executeUpdate(s);
            s = String.format("insert into %1s values(%2d, '%3s');", table, key);
            sql.executeUpdate(s);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static String getString(Statement sql, String table, String key, String defaultValue) {
        String s = String.format("select * from %1s where key='%2s'", table, key);
        ResultSet rs;
        try {
            rs = sql.executeQuery(s);
            if (rs.next()) return rs.getString("value");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return defaultValue;
    }

    public static int putString(Statement sql, String table, String key) {
        String s = String.format("select * from %1s where key='%2s'", table, key);
        Log.i("query:" + s);
        ResultSet rs;
        try {
            rs = sql.executeQuery(s);
            if (rs.next()) return rs.getInt("value");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static String printTable(Statement sql, String table) throws SQLException {
        ResultSet rs = sql.executeQuery("select * from " + table);
        StringBuilder sb = new StringBuilder();
        while (rs.next()) {
            ResultSetMetaData rsm = rs.getMetaData();
            for (int i = 1; i <= rsm.getColumnCount(); i++) {
                sb.append(rsm.getColumnName(i) + ":" + rs.getObject(i));
                sb.append("   ");
            }
            sb.append('\n');
        }
        return sb.toString();
    }

    public static void main(String[] args) throws ClassNotFoundException {
        Connection connection = null;
        try {
            // create a database connection
            connection = sqliteGetConnection("hello");
            Statement statement = connection.createStatement();
            // statement.setQueryTimeout(30); // set timeout to 30 sec.
            //
            // // statement.executeUpdate("drop table if exists person");
            // statement.executeUpdate("create table if not exists asd (id, name)");
            // statement.executeUpdate("insert into person values(1, 'leo')");
            // statement.executeUpdate("insert into person values(2, 'yui')");
            // //
            // ResultSet rs = statement.executeQuery("select * from person");
            // while (rs.next()) {
            // // read the result set
            // System.out.println("name = " + rs.getString("name"));
            // System.out.println("id = " + rs.getInt("id"));
            // }
            createMapTable(statement, "hello");
            // getInt(statement, "hello", "lrscp");
            Log.i(printTable(statement, "hello"));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) connection.close();
            } catch (SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

}
